home *** CD-ROM | disk | FTP | other *** search
/ Future Workshop / Future Workshop.iso / utility / formula1 / vcform1.3 / VB4 / ODBC2 / odbc2.frm (.txt) < prev    next >
Encoding:
Visual Basic Form  |  1995-09-15  |  15.8 KB  |  351 lines
  1. VERSION 4.00
  2. Begin VB.Form Form1 
  3.    Caption         =   "Double Click On Data in Location Tab for Drill Down"
  4.    ClientHeight    =   5220
  5.    ClientLeft      =   1365
  6.    ClientTop       =   1755
  7.    ClientWidth     =   6795
  8.    Height          =   5970
  9.    Icon            =   "odbc2.frx":0000
  10.    Left            =   1275
  11.    LinkTopic       =   "Form1"
  12.    ScaleHeight     =   5220
  13.    ScaleWidth      =   6795
  14.    Top             =   1095
  15.    Width           =   6975
  16.    Begin VB.Timer tmrInit 
  17.       Enabled         =   0   'False
  18.       Interval        =   500
  19.       Left            =   4020
  20.       Top             =   2640
  21.    End
  22.    Begin ComctlLib.StatusBar StatusBar1 
  23.       Align           =   2  'Align Bottom
  24.       Height          =   240
  25.       Left            =   0
  26.       TabIndex        =   1
  27.       Top             =   4980
  28.       Width           =   6795
  29.       _Version        =   65536
  30.       _ExtentX        =   11986
  31.       _ExtentY        =   423
  32.       _StockProps     =   68
  33.       AlignSet        =   -1  'True
  34.       SimpleText      =   ""
  35.       _timers         =   1
  36.       NumPanels       =   2
  37.       i1              =   "odbc2.frx":044A
  38.       i2              =   "odbc2.frx":0566
  39.    End
  40.    Begin VCIF1Lib.F1Book F1Book1 
  41.       Height          =   4815
  42.       Left            =   0
  43.       TabIndex        =   0
  44.       Top             =   0
  45.       Width           =   6555
  46.       _version        =   65536
  47.       _extentx        =   11562
  48.       _extenty        =   8493
  49.       _stockprops     =   96
  50.       borderstyle     =   1
  51.       appname         =   ""
  52.       filename        =   "odbc2.frx":06B6
  53.    End
  54.    Begin VB.Menu mnuFile 
  55.       Caption         =   "&File"
  56.       Begin VB.Menu mnuFileExit 
  57.          Caption         =   "E&xit"
  58.       End
  59.    End
  60.    Begin VB.Menu mnuDataAnalysis 
  61.       Caption         =   "&Data Analysis"
  62.       Begin VB.Menu mnuDataAnalysisType 
  63.          Caption         =   "&Sum"
  64.          Checked         =   -1  'True
  65.          Index           =   0
  66.          Tag             =   "SUM"
  67.       End
  68.       Begin VB.Menu mnuDataAnalysisType 
  69.          Caption         =   "&Average"
  70.          Index           =   1
  71.          Tag             =   "Average"
  72.       End
  73.       Begin VB.Menu mnuDataAnalysisType 
  74.          Caption         =   "Standard &Deviation"
  75.          Index           =   2
  76.          Tag             =   "STDEV"
  77.       End
  78.    End
  79.    Begin VB.Menu mnuHelp 
  80.       Caption         =   "Help!"
  81.    End
  82. Attribute VB_Name = "Form1"
  83. Attribute VB_Creatable = False
  84. Attribute VB_Exposed = False
  85. '' About the tables:
  86. ''    The ODBC2 database contains three tables:
  87. ''    Location_Table
  88. ''       ID          Counter
  89. ''       City        Text  50
  90. ''       State       Text  50
  91. ''       Region      Text  50
  92. ''    Employee_Table
  93. ''       ID          Counter
  94. ''       First_Name  Text  50
  95. ''       Last_Name   Text  50
  96. ''       Location    Integer
  97. ''    Sales_Table
  98. ''       ID          Counter
  99. ''       Date        dd-mmm-yy
  100. ''       Sales       Currency
  101. Option Explicit
  102. Dim DataSourceName$     ' Initialized in Form Load
  103. Dim DataConnectString$  ' Initialized in Form Load
  104. Private Sub F1Book1_DblClick(ByVal nRow As Long, ByVal nCol As Long)
  105.    '' This section implements the drill down aspect of the project. On startup, the
  106.    '' user will see the contents of the Location_Table. If they double click on a
  107.    '' column heading, a new sheet is added and a query based on the column header
  108.    '' is performed and loaded into the new sheet. If the user double clicks on a
  109.    '' cell, a new sheet is added, a query is made based on the contents of the cell
  110.    '' and placed in the sheet.
  111.    Dim query$, tabName$, funcNum%, i%
  112.    '' The queries look complex but were created in a minute by opening the ODBC2.MDB
  113.    '' in Access, creating the query with the query builder, switching to SQL mode,
  114.    '' and copying the result here. Although predefined queries would be faster, they
  115.    '' would require much more work and be much less flexible. First you would have
  116.    '' to generate queries for each item you would allow the use to drill on. Next
  117.    '' you would have to get the item the user clicked on and somehow transform that
  118.    '' into the name of the query you generated. Next you have to worry about records
  119.    '' being deleted, added, or modified. What happens if you add an employee or
  120.    '' location? Do you create new queries? Do you have to modify your code?.
  121.    '' The method used here avoids all this extra work at a relatively small
  122.    '' performance penalty.
  123.    ''
  124.    '' How to make the queries:
  125.    '' We know the tables as defined above. We also know that the first table they
  126.    '' see is the Location_Table so to start with, lets give the user a crosstab
  127.    '' that shows area versus date with sum of sales as the data. We do a bottom up
  128.    '' design and evolve it to fit our needs. Lets say the user clicks on the column
  129.    '' header for the city field in the Location_Table. To handle this case, first build
  130.    '' a crosstab in Access for city vs date and copy it here. This will handle the
  131.    '' case of the user double clicking in the City column header. If we replace single
  132.    '' occurance of city with the column header text the user clicked on (with ColText
  133.    '' method) we have a more versatile query that will fetch by city, state, or region.
  134.    ''
  135.    '' Now Evolve:
  136.    '' The above will generate 3 queries, but what if the user wants data for an
  137.    '' individual city. As a response, we can stick with the crosstab theme and provide
  138.    '' sales by person. Back to Access to build another query. We make this a crosstab
  139.    '' that shows Last_Name in the columns, date in rows, sum of sales as data, all
  140.    '' for the city of New Haven. Switch to SQL mode and copy the text here. For this
  141.    '' query we replace all occurances of City with the text from the column of the
  142.    '' cell the user clicked on and the occurance of New Haven with the text from the
  143.    '' cell the user double clicked on (TextRC method).
  144.    ''
  145.    '' SPECIAL NOTE: you use SINGLE QUOTES for a string in this query where Access
  146.    '' uses double quotes.
  147.    ''
  148.    '' How to tell which query to run? Simply check which row was double clicked on.
  149.    '' If nRow = 0, it was a column header so use the first query. Otherwise use the
  150.    '' second query.
  151.    ''
  152.    '' So now, with two "parameterized" queries in code below, we can generate 29
  153.    '' different sales crosstabs. Not too bad for this little function.
  154.    ''
  155.    '' Error Checking:
  156.    '' Of course we want some limit to the havoc the user can play with this simple
  157.    '' demo, so we limit the queries to double clicks in the data cells or the
  158.    '' column headers over the data cells.
  159.    With F1Book1
  160.       .MousePointer = F1Hourglass
  161.       StatusBar1.Panels(1).Text = "Adding New Sheet..."
  162.       StatusBar1.Refresh
  163.       
  164.       If .Sheet = 1 Then
  165.          If nRow <= .LastRow And nCol > 0 And nCol <= .LastCol Then
  166.             '' Crosstab of sales by City, State, Region if click on col header
  167.             If (nRow = 0) Then
  168.                Let query = "TRANSFORM Sum(Sales_Table.Sales) AS SumOfSales " & _
  169.                   "SELECT Sales_Table.Date FROM Sales_Table INNER JOIN " & _
  170.                   "(Employee_Table INNER JOIN Location_Table ON " & _
  171.                   "Employee_Table.Location = Location_Table.ID) ON " & _
  172.                   "Sales_Table.Employee = Employee_Table.ID GROUP BY " & _
  173.                   "Sales_Table.Date PIVOT Location_Table." & .ColText(nCol) & ";"
  174.                Let tabName = "Sales By " & .ColText(nCol)
  175.                .SetSelection 1, nCol, 1, nCol
  176.                StatusBar1.Panels(1).Text = "Fetching Data for " & .ColText(nCol) & "..."
  177.             Else
  178.                '' Crosstab of area if clicked on cell
  179.                Let query = "TRANSFORM Sum(Sales_Table.Sales) AS SumOfSales " & _
  180.                   "SELECT Sales_Table.Date FROM Sales_Table INNER JOIN " & _
  181.                   "(Employee_Table INNER JOIN Location_Table ON Employee_Table.Location " & _
  182.                   "= Location_Table.ID) ON Sales_Table.Employee = Employee_Table.ID " & _
  183.                   "WHERE ((Location_Table." & .ColText(nCol) & " = '" & _
  184.                   .TextRC(nRow, nCol) & "')) GROUP BY Sales_Table.Date, " & _
  185.                   "Location_Table." & .ColText(nCol) & " PIVOT Employee_Table.Last_Name;"
  186.                Let tabName = "Sales In " & .TextRC(nRow, nCol)
  187.                StatusBar1.Panels(1).Text = "Fetching Data for " & .TextRC(nRow, nCol) & "..."
  188.             End If
  189.             
  190.             StatusBar1.Refresh
  191.             
  192.             '' Add a sheet, make it active, set the tab name
  193.             .InsertSheets .NumSheets + 1, 1
  194.             .Sheet = .NumSheets
  195.             .SheetName(.Sheet) = tabName
  196.             
  197.             Call Fetch(F1Book1, .Sheet, 1, 1, DataConnectString, query, True, True, True, False)
  198.             '' Need to determine which data analysis to use
  199.             funcNum = 0
  200.             For i = 0 To 2
  201.                If mnuDataAnalysisType(i).Checked Then funcNum = i
  202.             Next i
  203.             StatusBar1.Panels(1).Text = "Calculating Summary info and formatting..."
  204.             StatusBar1.Refresh
  205.             Call SetRowColCalc(F1Book1, mnuDataAnalysisType(funcNum).Tag, 1, .LastRow, 2, .LastCol)
  206.             '' LastCol is now the sum column
  207.             Call NameAndFormatColumn(F1Book1, .Sheet, .LastCol, "Total Sales", "$#,##0.00")
  208.             Call NameAndFormatColumn(F1Book1, .Sheet, 1, .ColText(1), "dd-mmm-yy")
  209.             Call NameAndFormatRow(F1Book1, .Sheet, .LastRow, "Total Sales", "$#,##0.00")
  210.             
  211.             Call FormatSalesCrossTab(F1Book1, .Sheet)
  212.          End If
  213.       End If
  214.       .MousePointer = F1Default
  215.       StatusBar1.Panels(1).Text = "Ready..."
  216.    End With
  217. End Sub
  218. Private Sub Form_Load()
  219.    Let DataSourceName = "FO_ODBC2"
  220.    '' See Accessing External Databases in the VB4 help for
  221.    '' more info on creating this string (Professional Version).
  222.    '' When using this string in the ODBCConnect method, Formula
  223.    '' One will prompt for any information that is not provided by
  224.    '' either the data source or the string. For this project we
  225.    '' set all information for the data source with SQLConfigDataSource
  226.    '' and just tell Formula One to use ODBC and the data src name.
  227.    ''
  228.    '' These are done in a timer so the form can be refreshed and
  229.    '' the load time doesn't look so long. Two things take a while
  230.    '' to do - registering the database and getting an open
  231.    '' connection to it. After that everything is quite speedy.
  232.    '' You will probably want to hide this activity in a timer
  233.    '' also. The user will probably stare at the screen for three
  234.    '' or four seconds before starting anyway. Put up a help
  235.    '' screen or introduction to keep them busy.
  236.    Let DataConnectString = "ODBC;DSN=" & DataSourceName
  237.    '' Now start the timer for the help and the DSN and fetch
  238.    'tmrHelp.Enabled = True
  239.    tmrInit.Enabled = True
  240. End Sub
  241. Private Sub Form_Resize()
  242.    F1Book1.Width = ScaleWidth
  243.    F1Book1.Height = ScaleHeight - StatusBar1.Height
  244. End Sub
  245. Private Sub mnuDataAnalysisType_Click(Index As Integer)
  246. '' Indices are
  247. ''    0 Sum
  248. ''    1 Average
  249. ''    2 Standard Deviation
  250.    Dim i%
  251.    For i = 0 To 2
  252.       mnuDataAnalysisType(i).Checked = False
  253.    Next i
  254.    mnuDataAnalysisType(Index).Checked = True
  255.    If F1Book1.Sheet <> 1 Then
  256.       Call SetRowColCalc(F1Book1, mnuDataAnalysisType(Index).Tag, _
  257.          1, F1Book1.LastRow - 1, 2, F1Book1.LastCol - 1)
  258.       Call FormatSalesCrossTab(F1Book1, F1Book1.Sheet)
  259.    End If
  260.       
  261. End Sub
  262. Private Sub mnuFileExit_Click()
  263.    End
  264. End Sub
  265. Private Sub mnuHelp_Click()
  266.    Dim msg$
  267.    StatusBar1.Panels(1).Text = "Helping..."
  268.    Let msg = "This data drill down example constructs queries on the fly " & _
  269.       "based on a double click on the spreadsheet with the tab name Location. " & _
  270.       "For example, double clicking on the column header named city will generate " & _
  271.       "a crosstab that shows sales in all cities over dates. A double click on " & _
  272.       "a cell containing New Haven will generate a query for all sales staff in " & _
  273.       "New Haven over dates covered in the database. " & Chr$(13) & Chr$(13) & _
  274.       "A new sheet is added to the workbook and filled with the query results. " & _
  275.       "The bottom row and right column contain simple data analysis whose default " & _
  276.       "is a sum. You can change this analysis for the active sheet and all future " & _
  277.       "by making a selection in Data Analysis menu."
  278.       
  279.    MsgBox msg
  280.    StatusBar1.Panels(1).Text = "Ready..."
  281. End Sub
  282. Private Sub tmrInit_Timer()
  283.    Dim dbDriver$, dbAttributes$
  284.    Dim result As Boolean
  285.    tmrInit.Enabled = False
  286.    MousePointer = 11
  287.    '' The VB method DBEngine.RegisterDatabase will register
  288.    '' the data source name but not the database name or system
  289.    '' database name. There are four choices for doing this: Let
  290.    '' the user figure it out at run time, Write to the INI and
  291.    '' Registry yourself, Configure the data source ahead of time
  292.    '' with the ODBC admin 32 program, or use DLL calls as shown
  293.    '' in the example below. One warning - the VB DBEngine methods
  294.    '' are also very slow compared to the example below.
  295.    '' The following is left in as a curiosity...
  296.    '' Adds an entry to the ODBC.INI file and registry. Should be
  297.    '' done one time prior to use. Can be called every time
  298.    '' and only one entry will be made, but this slows load
  299.    '' time and destroys the settings for an existing
  300.    '' datasource that are made with the ODBC admin
  301.    '' 32 program (such as database name, system.mda).
  302.    'DBEngine.IniPath = App.Path & "\ODBC2.INI"
  303.    'DBEngine.RegisterDatabase DataSourceName, _
  304.       "Microsoft Access Driver (*.mdb) (32 bit)", _
  305.       True, "Driver32=C:\WINNT35\System32\odbcjt32.dll"
  306.    '' The following sets up a Data Source name and all the
  307.    '' attributes that are necessary to connect to the data
  308.    '' source without showing a dialog. The DSN is reconfigured
  309.    '' each time but this is pretty fast. The attributes must
  310.    '' be in null terminated keyword-value pairs with two nulls
  311.    '' at the end of the string.
  312.    '' Passing Form1.hWnd will generate a dialog for the user to
  313.    '' modify the info provided but, if you want the user to do
  314.    '' this, you can just use the Formula One generated dialogs
  315.    '' to create a new data source and skip all the dll stuff.
  316.    '' See odbc2.bas for more info.
  317.    StatusBar1.Panels(1).Text = "Registering New Database..."
  318.    StatusBar1.Refresh
  319.    Let dbDriver = "Microsoft Access Driver (*.mdb)"
  320.    Let dbAttributes = "DSN=" & DataSourceName & Chr$(0) _
  321.       & "DBQ=" & App.Path & "\ODBC2.MDB" & Chr$(0) _
  322.       & "SystemDB=" & App.Path & "\system.mda" & Chr$(0) _
  323.       & "DefaultDir=" & App.Path & Chr$(0) _
  324.       & "UID=admin" & Chr$(0) _
  325.       & "PWD=" & Chr$(0) & Chr$(0)
  326.    '' If the next statement fails, we can't get to the database without
  327.    '' the user going through some hoops, so we exit.
  328.    result = SQLConfigDataSource(0, ODBC_ADD_DSN, dbDriver, dbAttributes)
  329.    If (False = result) Then
  330.       MsgBox "Establishing DSN failed! Error: " & result
  331.       End
  332.    End If
  333.    StatusBar1.Panels(1).Text = "Fetching Initial Table..."
  334.    StatusBar1.Refresh
  335.    '' Startup with the Location Table
  336.    Call Fetch(F1Book1, 1, 1, 1, DataConnectString, _
  337.       "Select City, State, Region From Location_Table", True, True, True, True)
  338.    Call MakeYellowGreenBars(F1Book1, 1)
  339.    With F1Book1
  340.       .Left = 0
  341.       .Top = 0
  342.       .SetSelection 1, 1, .LastRow, .LastCol
  343.       .SetBorder 5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
  344.       .Selection = "A1"
  345.       .SheetName(1) = "Locations"
  346.    End With
  347.    StatusBar1.Panels(1).Text = "Ready..."
  348.    StatusBar1.Refresh
  349.    MousePointer = 0
  350. End Sub
  351.